﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Aulac.Rsc.Bss.Tags;
using Aulac.Rsc.Bss;
using Aulac.Global.Common;
using Aulac.Global.Xml;
using Aulac.Srv.Data.XmlQuery;
using System.Xml;
using Aulac.Srv.Db.Common;
using System.Data;

namespace Aulac.Srv.Db.Data.Ems.Items.RptLists
{
    partial class TabDepr
    {
        /// <summary>
        /// ExecQuery Chi tiết một bảng Khấu hao.
        /// </summary>
        private XmlDataDocument ExecQueryGetDetailTabDepr(string pTabDeprPrkID)
        {
            try
            {
                SrvExecXmlQueryData query = new SrvExecXmlQueryData(DbDataProvider);

                query.SetVchHeaderElm(QueryCmdTypes.SelectData);
                string sqlQueryH = string.Format("SELECT {0}", StringHelper.ConvertArrayToString(", ", TabDeprInfo.mLstTabHeaderDepr.ToArray()));
                sqlQueryH += string.Format(" FROM {0}", TabDeprInfo.mTbTabDeprHeader);
                sqlQueryH += string.Format(" WHERE {0} = @{0}", tagTab.TabHeaderDepr.TabDeprPrkID);
                query.ObjQueryVchHeader.SqlQueryString = sqlQueryH;
                query.ObjQueryVchHeader.AddValueParamFilterItem(tagTab.TabHeaderDepr.TabDeprPrkID, tagTab.TabHeaderDepr.TabDeprPrkID, pTabDeprPrkID);

                query.SetVchLinesElm(QueryCmdTypes.SelectData);

                string sqlQueryL = string.Format("SELECT TlD.CapAssPrkID, TlD.DeptPrkID, TlD.OriginalPrice, TlD.DeprRateMonth, TlD.DeprValue, TlD.DeprValueMonth, dbo.Dm_Department.DeptID, dbo.Dm_CapAss.CapAssName, dbo.Dm_CapAss.CapAssID");
                sqlQueryL += string.Format(" FROM {0} AS TlD LEFT OUTER JOIN dbo.Dm_CapAss ON TlD.CapAssPrkID = dbo.Dm_CapAss.CapAssPrkID LEFT OUTER JOIN dbo.Dm_Department ON TlD.DeptPrkID = dbo.Dm_Department.DeptPrkID", TabDeprInfo.mTbTabDeprLine);
                sqlQueryL += string.Format(" WHERE (TlD.{0} = @{0})", tagTab.TabLineDepr.TabDeprPrkID);
                query.ObjQueryVchLines.SqlQueryString = sqlQueryL;
                query.ObjQueryVchLines.AddValueParamFilterItem(tagTab.TabLineDepr.TabDeprPrkID, tagTab.TabLineDepr.TabDeprPrkID, pTabDeprPrkID);
            
                
                return query.ExecureQuery();
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        /// <summary>
        /// ExecQuery Danh sách các bảng Khấu hao.
        /// </summary>
        private XmlDataDocument ExecQueryGetListTabDepr(string pTabDateFrom, string pTabDateTo)
        {
            string sqlQuery = string.Format("SELECT h.{0}", StringHelper.ConvertArrayToString(" ,h.", TabDeprInfo.mLstTabHeaderDepr.ToArray()));
            sqlQuery += string.Format(" FROM {0} AS h ", TabDeprInfo.mTbTabDeprHeader);
            sqlQuery += string.Format(" WHERE (h.{0} BETWEEN ? AND ?)", tagTab.TabHeaderDepr.TabDate);
            try
            {
                DbDataReader = DbDataProvider.ExecuteReader(mDbCmd, sqlQuery, pTabDateFrom, pTabDateTo);
                XmlDataDocument xmlDoc = CreateXmlDocument("", "");
                XmlElement eVLines = XmlProcess.AddVchLinesElm(ref xmlDoc);
                while (DbDataReader.Read())
                {
                    XmlElement eLine = XmlProcess.AddVchLineItemElm(eVLines);
                    DbDataProvider.GetValuesToXml(DbDataReader, ref eLine, TabDeprInfo.mLstTabHeaderDepr.ToArray());
                }
                DbDataReader.Close();
                return xmlDoc;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        /// <summary>
        /// Add một bảng Khấu hao.
        /// </summary>
        private XmlDataDocument ExecQueryAddTabHeaderDepr(XmlElement eTmpH)
        {
            try
            {
                SrvExecXmlQueryData query = new SrvExecXmlQueryData(this.DbDataProvider);
                XmlElement eHeader = query.SetVchHeaderElm();
                query.ObjQueryVchHeader.QueryCmdType = QueryCmdTypes.AddDataTable;
                query.ObjQueryVchHeader.AddFieldPrk(tagTab.TabHeaderDepr.TabDeprPrkID, DbType.Decimal, KeyTypes.AutoNumber);
                query.ObjQueryVchHeader.TableName = TabDeprInfo.mTbTabDeprHeader;
                query.ImportData(query.ObjQueryVchHeader, ref eHeader, eTmpH);
                return query.ExecureQuery();
            }
            catch (Exception Ex )
            {
                throw Ex;
            }
        }

        /// <summary>
        /// Cap nhat mot bảng khấu hao.
        /// </summary>


        private XmlDataDocument ExecQueryUpdateHeaderTabDepr(string pTabDeprPrkID, XmlElement eTmpH)
        {
            try
            {
                SrvExecXmlQueryData query = new SrvExecXmlQueryData(this.DbDataProvider);
                XmlElement eHeader = query.SetVchHeaderElm(QueryCmdTypes.UpdateDataTable);
                query.ObjQueryVchHeader.AddFieldPrk(tagTab.TabHeaderDepr.TabDeprPrkID, DbType.Decimal, KeyTypes.AutoNumber);
                query.ObjQueryVchHeader.TableName = TabDeprInfo.mTbTabDeprHeader;
                query.ImportData(query.ObjQueryVchHeader, ref eHeader, eTmpH);
                query.ObjQueryVchHeader.AddValueParamFilterItem(tagTab.TabHeaderDepr.TabDeprPrkID, tagTab.TabHeaderDepr.TabDeprPrkID, pTabDeprPrkID);
                XmlDataDocument xmlTest = query.ExecureQuery();
                return xmlTest;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        private void ExecQueryAddTabLineDepr(XmlNodeList pListLine, string pTabDeprPrkID)
        {
            try
            {
                SrvExecXmlQueryData query = new SrvExecXmlQueryData(this.DbDataProvider);                
                XmlElement eVLines = query.SetVchLinesElm(QueryCmdTypes.AddDataTable);
                query.ObjQueryVchLines.AddFieldPrk(tagTab.TabHeaderDepr.TabDeprPrkID, DbType.Decimal, KeyTypes.None, RefValueTypes.InnerLineElm);
                query.ObjQueryVchLines.TableName = TabDeprInfo.mTbTabDeprLine;

                foreach (XmlElement iL in pListLine)
                {
                    XmlElement eLine = XmlProcess.AddVchLineItemElm(eVLines);
                    query.ImportData(query.ObjQueryVchLines, ref eLine, iL);
                    XmlProcess.SetAtrb(ref eLine, tagTab.TabHeaderDepr.TabDeprPrkID, pTabDeprPrkID);
                }                
                query.ExecureQuery();
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }
        /// <summary>
        /// Xoa mot Phieu mua hang.
        /// </summary>
        private XmlDataDocument ExecQueryDelTabDepr(string sTabDeprPrkID)
        {
            try
            {
                string sqlQureyH = string.Format("DELETE FROM {0}",  TabDeprInfo.mTbTabDeprHeader);
                sqlQureyH += string.Format(" WHERE {0} = ?", tagTab.TabHeaderDepr.TabDeprPrkID);
                string sqlQueryL = string.Format("DELETE FROM {0}", TabDeprInfo.mTbTabDeprLine);
                sqlQueryL += string.Format(" WHERE {0} = ?", tagTab.TabLineDepr.TabDeprPrkID);
                try
                {
                    DbDataProvider.ExecuteNonQuery(ref mDbCmd, sqlQureyH, sTabDeprPrkID);
                    XmlDataDocument xmlDoc = CreateXmlDocument("", "");
                    XmlProcess.SetAtrbVchHeader(xmlDoc, tagTab.TabHeaderDepr.TabDeprPrkID, sTabDeprPrkID);
                    DbDataProvider.ExecuteNonQuery(ref mDbCmd, sqlQueryL, sTabDeprPrkID);
                    return xmlDoc;
                }
                catch (Exception Ex)
                {
                    throw Ex;
                }
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }
        private bool DelTabDeprLine(string pTabDeprPrkID)
        {
            sqlQuery = SqlHelper.CreateSqlDelete(TabDeprInfo.mTbTabDeprLine, tagTab.TabLineDepr.TabDeprPrkID);
            return DbDataProvider.ExecuteNonQuery(ref mDbCmd, sqlQuery, pTabDeprPrkID) >= 0;
        }
        
    }
}
